package com.westline.unicom.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.westline.unicom.dao.BillDao;
import com.westline.unicom.datasource.DbContextHolder;
import com.westline.unicom.util.DateUtil;


public class BillDaoImpl extends BaseDaoImpl implements BillDao  {
	/**
	 * 判断是否已经绑定号码
	 */
	public boolean isHaveBingNum(String sn){
		String sql = "select * from xx_bill_binginfo  where sn=?";
		List<Object> projectList = this.jdbcTemplate.queryForList(sql, new Object[]{sn});
		if(projectList!=null && projectList.size()>0){
			return true;
		}else{
			return false;
		}
	}
	/*
	 * 取默认号码
	 */
	public Object getDefaultBingNum(String sn,String telnum){
		String sql = "select t.id,t.sn,t.bindnumber,t.isdefault,t.carrieroperator,t.ispostpay,t.balance,t.realtimecharge,t.arrearage  from xx_bill_binginfo t where t.sn=? and t.bindnumber=?";
		List<Object> projectList = this.jdbcTemplate.queryForList(sql, new Object[]{sn,telnum});
		if(projectList!=null && projectList.size()>0){
			return projectList.get(0);
		}else{
			return null;
		}
	}public Object getDefaultBingNum(String sn){
		String sql = "select t.id,t.sn,t.bindnumber,t.isdefault,t.carrieroperator,t.ispostpay,t.balance/100 as balance,t.realtimecharge/100 as realtimecharge,t.arrearage/100 as arrearage  from xx_bill_binginfo t where t.sn=? and t.isDefault=1";
		List<Object> projectList = this.jdbcTemplate.queryForList(sql, new Object[]{sn});
		if(projectList!=null && projectList.size()>0){
			return projectList.get(0);
		}else{
			return null;
		}
	}
	public Object getBingNumById(int bindId){
		String sql = "select t.id,t.sn,t.bindnumber,t.isdefault,t.carrieroperator,t.ispostpay,t.balance/100 as balance,t.realtimecharge/100 as realtimecharge,t.arrearage/100 as arrearage  from xx_bill_binginfo t where t.id=?";
		List<Object> projectList = this.jdbcTemplate.queryForList(sql, new Object[]{bindId});
		if(projectList!=null && projectList.size()>0){
			return projectList.get(0);
		}else{
			return null;
		}
	}
	public List getBingNumList(String sn){
		String sql = "select * from xx_bill_binginfo  where sn=?";
		return this.jdbcTemplate.queryForList(sql, new Object[]{sn});
	}
	/**
	 * 刷新绑定号码的实时信息
	 * @return
	 */
	public void reFlashNumInfo(String balance,String realtimecharge,String arrearage,String bindnum,String sn){
		String sql="update xx_bill_binginfo set BALANCE=?,REALTIMECHARGE=?,ARREARAGE=?,REFRESHTIME=sysdate where BINDNUMBER=? and sn=?";
		this.jdbcTemplate.update(sql,new Object[]{balance,realtimecharge,arrearage,bindnum,sn});
		
	}
	public void insertNumInfo(String sn,String bindnumber,int isdefault,int carrieroperator,int ispostpay,String balance,String realtimecharge,String arrearage){
		String sql="";
		if (isdefault==1){
			sql="update xx_bill_binginfo set isdefault=0 where sn=?";
			this.jdbcTemplate.update(sql,new Object[]{sn});
		}
		sql="insert into xx_bill_binginfo (id,sn,bindnumber,isdefault,carrieroperator,ispostpay,balance,realtimecharge,arrearage,refreshtime)";
		sql+=" values(xx_bill_binginfo_id.nextval,?,?,?,?,?,?,?,?,sysdate)";
		this.jdbcTemplate.update(sql,new Object[]{sn,bindnumber,isdefault,carrieroperator,ispostpay,balance,realtimecharge,arrearage});
		
	}
	public void updateNumInfo(String sn,int bindId,String bindnumber,int isdefault,int carrieroperator,int ispostpay,String balance,String realtimecharge,String arrearage){
		String sql="";
		if (isdefault==1){
			sql="update xx_bill_binginfo set isdefault=0 where sn=?";
			this.jdbcTemplate.update(sql,new Object[]{sn});
		}
		sql="update xx_bill_binginfo set bindnumber=?,isdefault=?,carrieroperator=?,ispostpay=?,balance=?,realtimecharge=?,arrearage=?,refreshtime=sysdate where id=?";
		this.jdbcTemplate.update(sql,new Object[]{bindnumber,isdefault,carrieroperator,ispostpay,balance,realtimecharge,arrearage,bindId});		
	}
	public void removeNumInfo(int bindId){
		String sql="";
		sql="delete from  xx_bill_binginfo where id=?";
		this.jdbcTemplate.update(sql,new Object[]{bindId});		
	}
	public void insertBillInfo(int bindid,int year,int month,String addedservice,String generationcharge,String message,String mobilemail,String monthlyfixed,String gprswap,String callerdisplay,String threegmonth,String totalconsumption,String totaldeduction,String totalpay){
		String sql="insert into xx_bill (id,bindid,year,month,addedservice,generationcharge,message,mobilemail,monthlyfixed,gprswap,callerdisplay,threegmonth,totalconsumption,totaldeduction,totalpay,refreshtime)";
		sql+=" values(xx_bill_id.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate)";
		this.jdbcTemplate.update(sql,new Object[]{bindid,year,month,addedservice,generationcharge,message,mobilemail,monthlyfixed,gprswap,callerdisplay,threegmonth,totalconsumption,totaldeduction,totalpay});
		
	}
	public void reFlashBillInfo(int bindid,int year,int month,String addedservice,String generationcharge,String message,String mobilemail,String monthlyfixed,String gprswap,String callerdisplay,String threegmonth,String totalconsumption,String totaldeduction,String totalpay){
		String sql="update  xx_bill set addedservice=?,generationcharge=?,message=?,mobilemail=?,monthlyfixed=?,gprswap=?,callerdisplay=?,threegmonth=?,totalconsumption=?,totaldeduction=?,totalpay=?,refreshtime=sysdate where bindid=? and year=? and month=?";
		this.jdbcTemplate.update(sql,new Object[]{addedservice,generationcharge,message,mobilemail,monthlyfixed,gprswap,callerdisplay,threegmonth,totalconsumption,totaldeduction,totalpay,bindid,year,month});
		
	}
	public Object getBill(int bindid,int year,int month){
		String sql = "select * from xx_bill where bindid=? and year=? and month=?";
		List<Object> projectList = this.jdbcTemplate.queryForList(sql, new Object[]{bindid,year,month});
		if(projectList!=null && projectList.size()>0){
			return projectList.get(0);
		}else{
			return null;
		}
	}
	

	public void insertBillHistoryInfo(int bindid,int year,int month,List<Map> billList){
		String sql="insert into xx_billhistory (id,billproject,billcost,billlevel,billorder,bindid,year,month,refrushtime)";
		sql+=" values(xx_bill_id.nextval,?,?,?,?,?,?,?,sysdate)";
		if (billList!=null && billList.size()>0)
			for (int i=0;i<billList.size();i++){
				Map billInfo=(Map)billList.get(i);
				this.jdbcTemplate.update(sql,new Object[]{billInfo.get("name").toString().replaceAll("--", "　"),billInfo.get("cost").toString(),billInfo.get("level").toString(),billInfo.get("order").toString(),bindid,year,month});
			}
		
		
	}

	public void reFlashBillHistoryInfo(int bindid,int year,int month,List<Map> billList){
		String sql="delete from xx_billhistory where bindid=? and year=? and month=?";
		this.jdbcTemplate.update(sql,new Object[]{bindid,year,month});
		sql="insert into xx_billhistory (id,billproject,billcost,billlevel,billorder,bindid,year,month,refrushtime)";
		sql+=" values(xx_bill_id.nextval,?,?,?,?,?,?,?,sysdate)";
		if (billList!=null && billList.size()>0)
			for (int i=0;i<billList.size();i++){
				Map billInfo=(Map)billList.get(i);
				this.jdbcTemplate.update(sql,new Object[]{billInfo.get("name").toString().replaceAll("--", "　"),billInfo.get("cost").toString(),billInfo.get("level").toString(),billInfo.get("order").toString(),bindid,year,month});
			}
		
		
	}
	public List getBillHistoryInfo(int bindid,int year,int month){
		String sql="select *  from xx_billhistory where bindid=? and year=? and month=?";
		return this.jdbcTemplate.queryForList(sql,new Object[]{bindid,year,month});		
	}
}
